/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.dao.util.MySqlDataAccessHelper;
import model.pojo.Product;

/**
 *
 * @author TiOc
 */
public class ProductDAO 
{
    //Đếm số sản phẩm theo thương hiệu
    public static int countProductFollowBrandName(int idBrandName)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd WHERE pd.idBrandName = %d", idBrandName);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm số sản phẩm theo dòng sản phẩm
    public static int countProductFollowCatalogue(int idCatalogue)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd WHERE pd.idCatalogue = %d", idCatalogue);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm sản phẩm theo dòng cpu
    public static int countProductFollowCurrentCpu(String nameCurrentCpu)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, ProductDetail As pddt, Cpu As c WHERE pd.idProduct = pddt.idProduct And pddt.idCpu = c.idCpu And c.detailCpu = '%s'", nameCurrentCpu); 
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm sản phẩm theo cpu
    public static int countProductFollowCpu(double sizeCpu)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, ProductDetail As pddt, Cpu As c WHERE pd.idProduct = pddt.idProduct And pddt.idCpu = c.idCpu And c.sizeCpu >= %f", sizeCpu);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm sản phẩm theo ram
    public static int countProductFollowRam(int sizeRam)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, ProductDetail As pddt, Ram As r WHERE pd.idProduct = pddt.idProduct And pddt.idRam =  r.idRam And r.sizeRam >= %d", sizeRam);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm sản phẩm theo hdd
    public static int countProductFollowHdd(int sizeHdd)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, ProductDetail As pddt, Hdd As h WHERE pd.idProduct = pddt.idProduct And pddt.idHdd =  h.idHdd And h.sizeHdd >= %d", sizeHdd);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm sản phẩm theo display
    public static int countProductFollowDisplay(double sizeDisplay)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, ProductDetail As pddt, Display As d WHERE pd.idProduct = pddt.idProduct And pddt.idDisplay = d.idDisplay And d.sizeDisplay >= %f", sizeDisplay);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    //Đếm sản phẩm theo Battery
    public static int countProductFollowCell(int sizeCell)
    {
        int quantity = 0;
        ArrayList<Product> ds = new ArrayList<Product>();
        String sql = String.format("SELECT Count(*) As quantity FROM Product As pd, ProductDetail As pddt, Battery As bt "
                + "WHERE pd.idProduct = pddt.idProduct And pddt.idBattery = bt.idBattery And bt.sizeBattery >= %d", sizeCell); 
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        ResultSet rs = helper.executeQuery(sql); 
        try
        {
            while(rs.next())
            {
                quantity = rs.getInt("quantity");
            }
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        helper.close();
        
        return quantity;
    }
    
    
    public static int laySoLuongSanPham()
    {
        String sql = String.format("SELECT COUNT(*) As SoLuong FROM Product");
        int soLuong = -1;
        try
        {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                soLuong = rs.getInt("SoLuong");
            }
            helper.close();
        }
        catch(SQLException ex)
        {
            System.out.print(ex.getMessage());
        }
        return soLuong;
    }
    
    public static int tinhSoLuongTrang()
    {
        int soTrang ;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int soLuong = ProductDAO.laySoLuongSanPham();
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if(soLuong % soLuongSanPhamTrenTrang != 0)
        {
            soTrang++; 
        }
        return soTrang;
    }
    
    public static ArrayList<Product> layDanhSachSanPhamCoPhanTrang(int trang)
    {
        ArrayList<Product> ds = new ArrayList<Product>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(1);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = String.format("SELECT * FROM Product LIMIT %d, %d", batDau, soSanPhamTrenTrang, "WHERE isPublish = '1'");
        try
        {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql); 
           
            while(rs.next())
            {
                Product pd = new Product();
                pd.setIdProduct(rs.getInt("idProduct"));
                pd.setNameProduct(rs.getString("nameProduct"));
                pd.setImage(rs.getString("image"));
                pd.setPrice(rs.getDouble("price"));
                pd.setWarranty(rs.getString("warranty"));
                pd.setSpecialOffer(rs.getString("specialOffer"));
                ds.add(pd);
            }
            helper.close();
        }
        catch(SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
   
        return ds;
    }
}
